<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>SQLite functions</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="SQLite, SQL, functions database">
<meta name="description" content="In this part of the SQLite tutorial,
we will cover functions.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content2">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>SQLite functions</h1>


<p>
In this part of the SQLite tutorial, we will cover SQLite built-in functions. There are three types
of functions in SQLite database. Core, aggregate and date &amp; time functions. 
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<p>
We will cover some functions from each group of SQLite functions. 
</p>


<h2>Core functions</h2>

<p>
In this group we have various diverse functios. Some are numerical functions, 
some work with text. Others do some very specific things. 
</p>

<pre class="code">
sqlite> SELECT sqlite_version() AS 'SQLite Version';
SQLite Version
--------------
3.7.15.1  
</pre>

<p>
The <code>sqlite_version()</code> function returns the version
of the SQLite library.
</p>

<pre class="code">
sqlite> SELECT random() AS Random;
Random             
-------------------
1056892254869386643   
</pre>

<p>
The <code>random()</code> function returns a pseudo-random integer 
between -9223372036854775808 and +9223372036854775807. 
</p>

<pre class="code">
sqlite> SELECT abs(11), abs(-5), abs(0), abs(NULL);
abs(11)             abs(-5)      abs(0)      abs(NULL) 
------------------  -----------  ----------  ----------
11                  5            0           NULL  
</pre>

<p>
The <code>abs()</code> function  returns the absolute value of the numeric argument.
</p>

<pre class="code">
sqlite> SELECT max(Cost), min(Cost) FROM Cars;
max(Cost)    min(Cost)  
-----------  -----------
350000       9000  
</pre>

<p>
In our example, the <code>max()</code> and <code>min()</code> functions return the 
most and the least expensive cars from the Cars table. 
</p>

<pre class="code">
sqlite> .width 18
sqlite> SELECT upper(Name) AS 'Names in capitals' FROM Friends;
Names in capitals 
------------------
JANE              
THOMAS            
FRANK             
ELISABETH         
MARY              
LUCY              
JACK  
</pre>

<p>
The <code>upper()</code> function converts characters into upper-case letters. 
</p>

<pre class="code">
sqlite> SELECT lower(Name) AS 'Names in lowercase' FROM Friends
   ...> WHERE Id IN (1, 2, 3);
Names in lowercase
------------------
jane              
thomas            
frank  
</pre>

<p>
With the <code>lower()</code> function we change the names of first three rows
into lower-case letters.
</p>

<pre class="code">
sqlite> SELECT length('ZetCode');
length('ZetCode') 
------------------
7 
</pre>

<p>
The <code>length()</code> function returns the length of a string.
</p>

<pre class="code">
sqlite> SELECT total_changes() AS 'Total changes';
Total changes
-------------
3    
</pre>

<p>
The <code>total_changes()</code> function returns the number of 
row changes caused by INSERT, UPDATE or DELETE statements since the current 
database connection was opened. In the current database connection, I have done
three INSERT statements, so the total changes is equal to three. 
</p>

<pre class="code">
sqlite> .width 5
sqlite> SELECT sqlite_compileoption_used('SQLITE_DEFAULT_FOREIGN_KEYS') AS 'FK';
FK   
-----
0  
</pre>

<p>
The <code>sqlite_compileoption_used()</code> function returns a boolean value, 
depending on whether or not that option was used during the build. In our case
we check if the FOREIGN KEY constraint is enforced by default. The function returns
0, which means that the constraint is not enforced by default. We use the PRAGMA
statement to change it. (PRAGMA foreign_keys = 1;)
</p>

<pre class="code">
sqlite> SELECT typeof(12), typeof('ZetCode'), typeof(33.2), typeof(NULL), 
   ...> typeof(x'345edb');
typeof(12)    typeof('ZetCode')   typeof(33.2)  typeof(NULL)  typeof(x'345edb')
------------  ------------------  ------------  ------------  -----------------
integer       text                real          null          blob  
</pre>

<p>
The <code>typeof()</code> function returns the data type of the argument.
</p>



<h2>Aggregate funcions</h2>

<p>
With aggregate functions, we get some statistical data. 
</p>

<p>
Let's recap, what we have in the Cars table. 
</p>

<pre class="code">
sqlite> SELECT * FROM Cars;
Id          Name        Cost      
----------  ----------  ----------
1           Audi        52642     
2           Mercedes    57127     
3           Skoda       9000      
4           Volvo       29000     
5           Bentley     350000    
6           Citroen     21000     
7           Hummer      41400     
8           Volkswagen  21600   
</pre>

<p>
Notice, that there are no duplicate records. 
</p>

<pre class="code">
sqlite> SELECT count(*) AS '# of cars' FROM Cars;
# of cars 
----------
8     
</pre>

<p>
The <code>count()</code> function returns the number of rows in the
table. In our table, we have eight cars. Assuming, there are no duplicates. 
</p>


<p>
In the Orders table, we have duplicate records of customers. 
</p>

<pre class="code">
sqlite> SELECT * FROM Orders;
Id          OrderPrice  Customer  
----------  ----------  ----------
1           1200        Williamson
2           200         Robertson 
3           40          Robertson 
4           1640        Smith     
5           100         Robertson 
6           50          Williamson
7           150         Smith     
8           250         Smith     
9           840         Brown     
10          440         Black     
11          20          Brown    
</pre>

<p>
Logically, each customer can make multiple orders. How do we count the number of
orders and how do we count the number of customers?
</p>


<pre class="code">
sqlite> SELECT count(Customer) AS '# of orders'  FROM Orders;
# of orders
-----------
11   
</pre>

<p>
This SQL statement returns the number of orders. To calculate the 
number of unique customers, we have to utilize the DISTINCT clause.
</p>


<pre class="code">
sqlite> SELECT count(DISTINCT Customer) AS '# of customers' FROM Orders;
# of customers
--------------
5   
</pre>

<p>
We have 5 customers in our Orders table. They made 11 orders.
</p>

<p>
Next we are going to demonstrate the difference between the
<code>count(*)</code> and <code>count(ColumnName)</code>. These function usages differ in the
way, how they handle NULL values. 
</p>

<pre class="code">
sqlite> .nullvalue NULL
</pre>

<p>
First, we change how sqlite3 shows NULL values. By default, the NULL value
is shown as empty string. 
</p>

<pre class="code">
sqlite> CREATE TABLE Testing(Id INTEGER);
sqlite> INSERT INTO Testing VALUES(1);
sqlite> INSERT INTO Testing VALUES(2);
sqlite> INSERT INTO Testing VALUES(3);
sqlite> INSERT INTO Testing VALUES(NULL);
sqlite> INSERT INTO Testing VALUES(NULL);
sqlite> SELECT * FROM Testing;
Id          
------------
1           
2           
3           
NULL        
NULL 
</pre>

<p>
Here we create table Testing with 3 numerical and 2 NULL values.
</p>


<pre class="code">
sqlite> SELECT count(*) AS '# of rows' FROM Testing;
# of rows 
----------
5  
</pre>

<p>
The <code>count(*)</code> returns the number of rows in the table.
It takes NULL values into account. 
</p>


<pre class="code">
sqlite> SELECT count(Id) AS '# of non NULL values' FROM Testing;
# of non NULL values
--------------------
3 
</pre>

<p>
The <code>count(Id)</code> counts only non NULL values. 
</p>


<pre class="code">
sqlite> SELECT avg(Cost) AS 'Average price' FROM Cars;
Average price     
------------------
72721.125  
</pre>

<p>
The <code>avg()</code> function returns the average value
of all non NULL records. In our example, we show the average price
of the car in the Cars table. 
</p>
 
<p>
Finally, we mention the <code>sum()</code> function. It does
a summation of all non NULL values. 
</p>

<pre class="code">
sqlite> SELECT sum(OrderPrice) AS Sum FROM Orders;
Sum     
--------
4930   
</pre>

<p>
Here we count the sum of all orders made by our customers. 
</p>


<h2>Date and time funcions</h2>

<p>
SQLite has functions for working with date and time. With these functions
we can use various time strings, modifiers and formats. 
</p>

<pre class="code">
sqlite> .header OFF
sqlite> SELECT date('now');
2013-01-09    
</pre>

<p>
The <code>date()</code> function with the <i>now</i> string returns
the current date.  
</p>

<pre class="code">
sqlite> SELECT datetime('now');
2013-01-09 13:01:0
</pre>

<p>
The <code>datetime()</code> function returns the current date and time. 
</p>

<pre class="code">
sqlite> SELECT time('now');
13:23:21  
</pre>

<p>
The <code>time()</code> function gives the current time.
</p>

<pre class="code">
sqlite> SELECT time(), time('now');
13:50:12    13:50:12   
sqlite> SELECT date('now'), date();
2013-01-09   2013-01-09
</pre>

<p>
The <i>now</i> string can be omitted.
</p>

<hr class="btm">

<p>
The first parameter of the <code>date()</code>, <code>time()</code> and <code>datetime()</code> 
functions is the time string. It can be followed by one or more modifiers.
</p>

<pre class="code">
sqlite> SELECT date('now', '2 months');
2013-03-09 
</pre>

<p>
In this example, '2 months' is a modifier. It adds two months to the current date. 
So the function returns the date two months from today.
</p>

<pre class="code">
sqlite> SELECT date('now', '-55 days');
2012-11-15
</pre>

<p>
Negative modifiers can be also used. In this example, we extract 55 days from 
today.
</p>

<pre class="code">
sqlite> SELECT date('now', 'start of year');
2013-01-01  
</pre>

<p>
Using the <i>start of year</i> modifier, we get the date of the start
of the year, e.g. the January 1st. 
</p>

<pre class="code">
sqlite> SELECT datetime('now', 'start of day');
2013-01-09 00:00:00  
</pre>

<p>
With the help of the <i>start of day</i> modifier, we get the beginning of
the current day. 
</p>

<pre class="code">
sqlite> SELECT date('now', 'weekday 6');
2013-01-12 
</pre>

<p>
The <i>weekday</i> modifier advances to the next date, where Sunday is 0, Monday 1, ..., 
Saturday 6. In this example, we get the date of the nearest Saturday.
</p>

<p>
The modifiers can be combined. 
</p>


<pre class="code">
sqlite> SELECT date('now', 'start of year', '10 months', 'weekday 4');
2013-11-07
</pre>

<p>
This SQL statement returns the first Thursday of the November for the current year. 
In this example, we used three modifiers. <i>start of year</i>, <i>+x months</i>
and <i>weekday x</i>. The <i>now</i> time string gives the current date. The
<i>start of year</i> shifts the date backwards to the beginning of the year. 
The <i>10 months</i> adds 10 months to the current month (January). Finally, the 
<i>weekday 4</i> modifier advances the date forward to the first Thursday. 
</p>

<hr class="btm">

<p>
The <code>strftime()</code> function returns the date and time formatted according to the format string 
specified as the first argument. The second parameter is the time string. It can be followed by one
or more modifiers.
</p>

<pre class="code">
sqlite> SELECT strftime('%d-%m-%Y');
09-01-2013 
</pre>

<p>
We can use the the <code>strftime()</code> function to return a date
in a different format. 
</p>

<pre class="code">
sqlite> SELECT 'Current day: ' || strftime('%d');
Current day: 09  
</pre>

<p>
This SQL statement returns the current day of the month. We used the 
<code>strftime()</code> function.
</p>

<pre class="code">
sqlite> SELECT 'Days to XMas: ' || (strftime('%j', '2013-12-24') -
   ...> strftime('%j', 'now'));
Days to XMas: 349 
</pre>

<p>
Here we have computed the number of days till Christmas. The %j modifier
gives the day of the year for the time string.
</p>


<p>
In this part of the SQLite tutorial, we worked with the built-in SQLite functions. 
</p>

<div class="center"> 
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* horizontal */
google_ad_slot = "1734478269";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 
</div> 
<br>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified January 9, 2013  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

